/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package myetl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author apuangr1
 */
public class DatabaseAgent {

    ArrayList<DestData> destDatas;
    String insertSql;
    String table;
    AppManager apm;
    String columns;
    String[] cols;
    PreparedStatement psAdd, psCheck;
    private PreparedStatement psUpdate;

    DatabaseAgent(AppManager apm) {
        try {
            this.apm = apm;
            this.table = this.apm.getDestTable();
            this.columns = this.apm.getDestColumns();
            cols = columns.split(",");
            for (int i = 0; i < cols.length; i++) {
                cols[i] = cols[i].trim();
//                System.out.println(cols[i]);
            }
            buildInsertStatement();

//            System.out.println(this.insertSql);
            psAdd = apm.getDestConn().prepareStatement(insertSql);
            //        retrieve();
            //        retrieve();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void buildInsertStatement() {
        insertSql = "insert into " + table + "  (" + columns + " ) values ( ";
        for (int i = 0; i < cols.length; i++) {
            if (i == cols.length - 1) {
                insertSql = insertSql + "?)";
            } else {
                insertSql = insertSql + "?,";
            }


        }
//        System.out.println(insertSql);
    }

    public void addData(DestData destdata) {
//        System.out.println("Inserter:" + destdata.getDecimal(19));
        this.buildUpdateStatement(destdata);
        buildCheckStatement(destdata);

        if (isExist(destdata)) {
            System.out.println("updating ..............");
            updateData(destdata);
            return;
        }
        for (int i = 1; i <= destdata.numberOfColumn; i++) {

            if (destdata.getColumnType(i).contains("CHAR")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getColumnName(i) + ":" + destdata.getString(i));
                    psAdd.setString(i, destdata.getString(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getColumnType(i).contains("DEC") || destdata.getColumnType(i).contains("NUMERIC")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getDecimal(i));
                    psAdd.setDouble(i, destdata.getDecimal(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (destdata.getColumnType(i).contains("INT")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getInteger(i));
                    psAdd.setInt(i, destdata.getInteger(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (destdata.getColumnType(i).contains("DATE")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getInteger(i));
                    psAdd.setDate(i, destdata.getDate(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        try {
            psAdd.addBatch();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void updateData(DestData destdata) {
//        System.out.println("Inserter:" + destdata.getString(10));
        this.buildUpdateStatement(destdata);

        int i;
        for (i = 1; i <= destdata.numberOfColumn; ++i) {

            if (destdata.getColumnType(i).contains("CHAR")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getColumnName(i) + ":" + destdata.getString(i));
                    psUpdate.setString(i, destdata.getString(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getColumnType(i).contains("DEC") || destdata.getColumnType(i).contains("NUMERIC")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getDecimal(i));
                    psUpdate.setDouble(i, destdata.getDecimal(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (destdata.getColumnType(i).contains("INT")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getInteger(i));
                    psUpdate.setInt(i, destdata.getInteger(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (destdata.getColumnType(i).contains("DATE")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getInteger(i));
                    psUpdate.setDate(i, destdata.getDate(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
//        i--;
        int keyCount;
//        System.out.println("DDDDDDDDDDD" + i);
        keyCount = 1;
        while (keyCount <= destdata.getNumberOfKey()) {
//        for (keyCount = i; i <= i + destdata.getNumberOfKey(); keyCount++) {

            if (destdata.getKeyType(keyCount).contains("CHAR")) {
                try {
                    System.out.println("test dbagent:" + keyCount + ":" + destdata.getKeyType(keyCount) + ":" + destdata.getKeyName(keyCount) + ":" + destdata.getStringKey(keyCount));
                    psUpdate.setString(i, destdata.getStringKey(keyCount));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getKeyType(keyCount).contains("DEC") || destdata.getKeyType(keyCount).contains("NUMERIC")) {
                try {
//                    System.out.println("test dbagent:" + keyCount + ":" + destdata.getKeyType(keyCount) + ":" + destdata.getDecimalKey(keyCount));
                    psUpdate.setDouble(i, destdata.getDecimalKey(keyCount));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getKeyType(keyCount).contains("INT")) {
                try {
//                    System.out.println("test dbagent:" + keyCount + ":" + destdata.getKeyType(keyCount) + ":" + destdata.getIntegerKey(keyCount));
                    psUpdate.setInt(i, destdata.getIntegerKey(keyCount));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getKeyType(keyCount).contains("DATE")) {
                try {
//                    System.out.println("test dbagent:" + i + ":" + destdata.getColumnType(i) + ":" + destdata.getInteger(i));
                    psAdd.setDate(i, destdata.getDateKey(keyCount));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            i++;
            keyCount++;
        }
        try {
            psUpdate.addBatch();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public boolean isExist(DestData destdata) {
        System.out.println("isExist .................");
        destdata.setKeyValue();

        for (int i = 1; i <= destdata.getNumberOfKey(); i++) {
            System.out.println("... isExist   " + destdata.getKeyType(i));
            if (destdata.getKeyType(i).contains("CHAR")) {
                try {
//                    System.out.println("isExist:" + i + ":" + destdata.getKeyType(i) + ":" + destdata.getKeyName(i) + ":" + destdata.getStringKey(i));
                    psCheck.setString(i, destdata.getStringKey(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getKeyType(i).contains("DEC") || destdata.getKeyType(i).contains("NUMERIC")) {
                try {
//                    System.out.println("isExist:" + i + ":" + destdata.getKeyType(i) + ":" + destdata.getDecimalKey(i));
                    psCheck.setDouble(i, destdata.getDecimalKey(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getKeyType(i).contains("INT")) {
                try {
//                    System.out.println("isExist:" + i + ":" + destdata.getKeyType(i) + ":" + destdata.getIntegerKey(i));
                    psCheck.setInt(i, destdata.getIntegerKey(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }
            if (destdata.getKeyType(i).contains("DATE")) {
                try {
//                    System.out.println("isExist:" + i + ":" + destdata.getKeyType(i) + ":" + destdata.getIntegerKey(i));
                    psCheck.setDate(i, destdata.getDateKey(i));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    System.exit(0);
                }
            }

        }
        System.out.println(" isexist setting value done .................");
        try {
            ResultSet rs = psCheck.executeQuery();
            System.out.println(" end is exist .................");
            return rs.next();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public void updateDB() {
        try {
            System.out.println(" inserting .............");
            psAdd.executeBatch();
//            
            System.out.println(" updating .............");
            psUpdate.executeBatch();
//            System.exit(0);
        } catch (NullPointerException nex) {

            nex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private void buildCheckStatement(DestData dest) {
        if (psCheck != null) {
            return;
        }
        try {
            String criteria = dest.getUpdateCriteria();
            String sql = "select " + columns + " from " + table + " where " + criteria;
            System.out.println(sql);
            psCheck = apm.getDestConn().prepareStatement(sql);

        } catch (SQLException ex) {
            ex.printStackTrace();
//            System.exit(0);
        }
    }

    public void buildUpdateStatement(DestData dest) {
        if (psUpdate != null) {
            return;
        }
        String updateStatement = "update " + table + " set  ";
        for (int i = 0; i < cols.length; i++) {
            if (i == cols.length - 1) {
                updateStatement = updateStatement + cols[i] + " = ? ";
            } else {
                updateStatement = updateStatement + cols[i] + " = ?,";
            }


        }
        updateStatement = updateStatement + " where " + dest.getUpdateCriteria();
        System.out.println(updateStatement);
        try {
            psUpdate = apm.getDestConn().prepareStatement(updateStatement);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
//        System.out.println(updateStatement);
    }

    public void run() {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
